﻿<?xml version="1.0" encoding="utf-8" ?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"><xsl:template match="/DataSet">CREATE PROCEDURE CORE_<xsl:value-of select="Table/TABLE_NAME"/>_Update<xsl:value-of select="Table/TABLE_NAME"/>
    <xsl:for-each select="Table">
        <xsl:choose>
        <xsl:when test="DATA_TYPE = 'nvarchar' and CHARACTER_MAXIMUM_LENGTH > '-1'">
            @<xsl:value-of select="COLUMN_NAME"/><xsl:text> </xsl:text><xsl:value-of select="DATA_TYPE"/>(<xsl:value-of select="CHARACTER_MAXIMUM_LENGTH"/>)<xsl:if test="position() != last()">,</xsl:if>
        </xsl:when>
        <xsl:when test="DATA_TYPE = 'nvarchar' and CHARACTER_MAXIMUM_LENGTH = '-1'">
          @<xsl:value-of select="COLUMN_NAME"/><xsl:text> </xsl:text><xsl:value-of select="DATA_TYPE"/>(MAX)<xsl:if test="position() != last()">,</xsl:if>
        </xsl:when>
        <xsl:when test="DATA_TYPE = 'decimal'">
            @<xsl:value-of select="COLUMN_NAME"/><xsl:text> </xsl:text><xsl:value-of select="DATA_TYPE"/>(<xsl:value-of select="NUMERIC_PRECISION"/>,<xsl:value-of select="NUMERIC_SCALE"/>)<xsl:if test="position() != last()">,</xsl:if>
        </xsl:when>
        <xsl:otherwise>
            @<xsl:value-of select="COLUMN_NAME"/><xsl:text> </xsl:text><xsl:value-of select="DATA_TYPE"/><xsl:if test="position() != last()">,</xsl:if>
        </xsl:otherwise>
        </xsl:choose>
    </xsl:for-each>
AS
BEGIN
UPDATE <xsl:value-of select="Table/TABLE_NAME"/> SET    <xsl:for-each select="Table"><xsl:if test="not(starts-with(CONSTRAINT_NAME,'PK'))">
    [<xsl:value-of select="COLUMN_NAME"/>] = @<xsl:value-of select="COLUMN_NAME"/><xsl:if test="position() != last()">,<xsl:text> </xsl:text>
    </xsl:if>
    </xsl:if>
</xsl:for-each>
WHERE<xsl:for-each select="Table">
    <xsl:if test="starts-with(CONSTRAINT_NAME,'PK')">
    [<xsl:value-of select="COLUMN_NAME"/>] = @<xsl:value-of select="COLUMN_NAME"/>
    </xsl:if>
</xsl:for-each>
END
GO</xsl:template></xsl:stylesheet>